/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBHandling;

import GUI.ConfigDB;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.Serializable;
import java.sql.*;
import javax.swing.JOptionPane;

/**
 *
 * @author NguyenPhuThien
 */
public class DBUtil implements Serializable {

    private String server;
    private String port;
    private String user;
    private String pass;
    private String dbName;
    private Connection conn;
    private Statement stm;
    private ResultSet rs;

    public DBUtil() {
        this.loadConfig("config.ims");
    }

    public DBUtil(String server, String port, String user, String pass, String dbName) {
        this.server = server;
        this.port = port;
        this.user = user;
        this.pass = pass;
        this.dbName = dbName;
    }

    public Connection connect() {
        String url = "jdbc:sqlserver://" + server + ":" + port + ";" + "databaseName=" + dbName + ";";
        try {
            conn = DriverManager.getConnection(url, user, pass);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Cannot connect to database \n" + ex.getMessage());
            return null;
        }
        return conn;
    }

    public boolean checkConnection() {
        conn = connect();
        if (conn != null) {
            return true;
        }
        return false;
    }

    public ResultSet executeSQL(String querySQL) throws SQLException {
        stm = connect().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        rs = stm.executeQuery(querySQL);
        return rs;
    }

    public boolean executeProcedureUpdate(String sql, Object[] object) throws Exception {
        try {
            CallableStatement call = connect().prepareCall(sql);
            for (int i = 0; i < object.length; i++) {
                call.setObject(i + 1, object[i]);
            }
            return call.executeUpdate() > 0;
        } catch (Exception ex) {
            throw ex;
        } finally {
            this.closeConnection();
        }
    }

    //close connection
    public void closeConnection() throws SQLException {
        if (conn != null) {
            conn.close();
        }
        if (stm != null) {
            stm.close();
        }
        if (rs != null) {
            rs.close();
        }
    }

    public void loadConfig(String fileName) {
        ObjectInputStream in = null;
        try {
            in = new ObjectInputStream(new BufferedInputStream(new FileInputStream(
                    fileName)));

            DBUtil dbu = (DBUtil) in.readObject();
            this.setUser(dbu.getUser());
            this.setServer(dbu.getServer());
            this.setPort(dbu.getPort());
            this.setPass(dbu.getPass());
            this.setDbName(dbu.getDbName());

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error: can not find file config", "Notice", JOptionPane.OK_OPTION);
            new ConfigDB(null, false, this).setVisible(true);
        } finally {
            try {
                in.close();
            } catch (IOException ex) {
                JOptionPane.showMessageDialog(null, "Error: Can not close inputstream", "Notice", JOptionPane.OK_OPTION);
            }
        }
    }

    public boolean saveConfig(String fileName) {
        File f = new File(fileName);
        if (!f.exists()) {
            try {
                f.createNewFile();
            } catch (IOException ex) {
                JOptionPane.showMessageDialog(null, "Error: can not create new file config", "Notice", JOptionPane.OK_OPTION);
                System.out.println(ex.getMessage());
                return false;
            }
        }
        ObjectOutputStream out = null;
        try {
            out = new ObjectOutputStream(new BufferedOutputStream(
                    new FileOutputStream(fileName)));
            out.writeObject(this);

        } catch (IOException ex) {
            JOptionPane.showMessageDialog(null, "Error: can not save file config", "Notice", JOptionPane.OK_OPTION);
            System.out.println(ex.getMessage());
            return false;
        } finally {
            try {
                out.close();
            } catch (IOException ex) {
                JOptionPane.showMessageDialog(null, "Error: Can not close outputstream", "Notice", JOptionPane.OK_OPTION);
            }
        }
        return true;
    }

    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }

    public String getPass() {
        return pass;
    }

    public void setPass(String pass) {
        this.pass = pass;
    }

    public String getPort() {
        return port;
    }

    public void setPort(String port) {
        this.port = port;
    }

    public String getServer() {
        return server;
    }

    public void setServer(String server) {
        this.server = server;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }
}
